/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.elcom.emocbc.dbmapping.persistence;

import com.elcom.emocbc.dbmapping.entities.TopicBroadcastSchedule;
import com.elcom.emocbc.dbmapping.tables.daos.TopicScheduleDao;
import com.elcom.emocbc.dbmapping.tables.pojos.TopicSchedule;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.jooq.Configuration;
import org.jooq.ConnectionProvider;
import org.jooq.SQLDialect;
import org.jooq.conf.Settings;
import org.jooq.conf.StatementType;
import org.jooq.impl.DefaultConfiguration;

/**
 *
 * @author GiangLT
 */
public class TopicSchedulePersistance extends TopicScheduleDao {

    public static final String PROGRAMS_STATUS_NO_PROGRAM = "No program";
    public static final String PROGRAMS_STATUS_BROADCASTED = "Broadcasted";
    public static final String PROGRAMS_STATUS_BROADCAST_FAILED = "Broadcast failed";
    public static final String PROGRAMS_STATUS_GETTING_PROGRAM = "Getting program";
    public static final String PROGRAMS_STATUS_PROGRAM_FILLED = "Program filled";

    public TopicSchedulePersistance() {
        ConnectionProvider connectionProvider = new DbConnectionProvider();
        Settings settings = new Settings();
        settings.setStatementType(StatementType.STATIC_STATEMENT);
        Configuration configuration = new DefaultConfiguration()
                .set(connectionProvider)
                .set(SQLDialect.ORACLE)
                .set(settings);
        this.setConfiguration(configuration);
    }

    public List<TopicBroadcastSchedule> fetchByZoneId(Long zoneId) throws SQLException {
        ResultSetHandler<List<TopicBroadcastSchedule>> resultSetHandler = new TopicBroadcastScheduleListHandler();
        Connection con = DbAccess.getInstance().getConnection();
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT ts.id "
                + "	,ts.TIME "
                + "	,ts.topic_id "
                + "	,ts.topic_name "
                + "	,ts.topic_random_config_id "
                + "	,ts.last_updated_time "
                + "	,tc.zone_id "
                + "	,tc.zone_name "
                + "     ,ts.program_status "
                + "     ,ts.program_status_updated_time "
                + " FROM topic_schedule ts "
                + "	,topic_random_config tc "
                + " WHERE ts.topic_random_config_id = tc.id"
                + " AND tc.zone_id = ? "
                + " ORDER BY ts.TIME";
        try {
            return qr.query(con, sql, resultSetHandler, zoneId);
        } finally {
            DbUtils.closeQuietly(con);
        }
    }
    
    public List<TopicSchedule> fetchByProgramStatusAndTime(String programStatus, String startTime, String endTime) throws SQLException {
        ResultSetHandler<List<TopicSchedule>> resultSetHandler = new TopicScheduleListHandler();
        Connection con = DbAccess.getInstance().getConnection();
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT * "
                + " FROM topic_schedule ts "
                + " WHERE ts.program_status = ? "
                + " AND ts.time BETWEEN ? AND ? "
                + " ORDER BY ts.TIME";
        try {
            return qr.query(con, sql, resultSetHandler, programStatus, startTime, endTime);
        } finally {
            DbUtils.closeQuietly(con);
        }
    }

    public int deleteByTopicRandomConfigId(String topicRandomConfigId) throws SQLException {
        Connection con = DbAccess.getInstance().getConnection();
        QueryRunner qr = new QueryRunner();
        String sql = "DELETE "
                + " FROM topic_schedule "
                + " WHERE topic_random_config_id = ?";
        try {
            return qr.update(con, sql, topicRandomConfigId);
        } finally {
            DbUtils.commitAndCloseQuietly(con);
        }
    }

    public List<TopicBroadcastSchedule> findAllSchedule() throws SQLException {
        ResultSetHandler<List<TopicBroadcastSchedule>> resultSetHandler = new TopicBroadcastScheduleListHandler();
        Connection con = DbAccess.getInstance().getConnection();
        QueryRunner qr = new QueryRunner();
        String sql = "SELECT ts.id "
                + "	,ts.TIME "
                + "	,ts.topic_id "
                + "	,ts.topic_name "
                + "	,ts.topic_random_config_id "
                + "	,ts.last_updated_time "
                + "	,tc.zone_id "
                + "	,tc.zone_name "
                + " FROM topic_schedule ts "
                + "	,topic_random_config tc "
                + " WHERE ts.topic_random_config_id = tc.id "
                + " ORDER BY ts.TIME";
        try {
            return qr.query(con, sql, resultSetHandler);
        } finally {
            DbUtils.closeQuietly(con);
        }
    }

    public int updateScheduleProgramStatus(long zoneId, String status, String time) throws SQLException {
        Connection con = DbAccess.getInstance().getConnection();
        QueryRunner qr = new QueryRunner();
        String sql = "UPDATE topic_schedule "
                + " SET program_status = ? "
                + "     ,last_updated_time = SYSDATE "
                + " WHERE time = ? "
                + "   AND zone_id = ? ";
        try {
            return qr.update(con, sql, status, time, zoneId);
        } finally {
            DbUtils.commitAndCloseQuietly(con);
        }
    }
    
    public int updateScheduleProgramStatus(String status, String startTime, String endTime) throws SQLException {
        Connection con = DbAccess.getInstance().getConnection();
        QueryRunner qr = new QueryRunner();
        String sql = "UPDATE topic_schedule "
                + " SET program_status = ? "
                + "     ,last_updated_time = SYSDATE "
                + " WHERE time BETWEEN ? AND ?";
        try {
            return qr.update(con, sql, status, startTime, endTime);
        } finally {
            DbUtils.commitAndCloseQuietly(con);
        }
    }
    
    public int updateBroadcastedProgramStatus(String newStatus, String startTime, String endTime) throws SQLException {
        Connection con = DbAccess.getInstance().getConnection();
        QueryRunner qr = new QueryRunner();
        String sql = "UPDATE topic_schedule "
                + " SET program_status = ? "
                + "     ,last_updated_time = SYSDATE "
                + " WHERE program_status = '" + TopicSchedulePersistance.PROGRAMS_STATUS_BROADCASTED + "' "
                + " AND time BETWEEN ? AND ? "
                + " AND TRUNC(last_updated_time) < TRUNC(SYSDATE)";
        try {
            return qr.update(con, sql, newStatus, startTime, endTime);
        } finally {
            DbUtils.commitAndCloseQuietly(con);
        }
    }
    
    public int updateNoProgramStatus(String newStatus, String startTime, String endTime) throws SQLException {
        Connection con = DbAccess.getInstance().getConnection();
        QueryRunner qr = new QueryRunner();
        String sql = "UPDATE topic_schedule "
                + " SET program_status = ? "
                + "     ,last_updated_time = SYSDATE "
                + " WHERE (program_status = '" + TopicSchedulePersistance.PROGRAMS_STATUS_NO_PROGRAM + "' "
                + "    OR program_status = '" + TopicSchedulePersistance.PROGRAMS_STATUS_BROADCAST_FAILED + "') "
                + " AND time BETWEEN ? AND ? ";
        try {
            return qr.update(con, sql, newStatus, startTime, endTime);
        } finally {
            DbUtils.commitAndCloseQuietly(con);
        }
    }
    
    private class TopicScheduleListHandler implements ResultSetHandler<List<TopicSchedule>> {

        @Override
        public List<TopicSchedule> handle(ResultSet rs) throws SQLException {
            List<TopicSchedule> result = new ArrayList<TopicSchedule>();
            while (rs.next()) {
                TopicSchedule schedule = new TopicSchedule();
                int i = 1;
                schedule.setId(rs.getString(i++));
                schedule.setTime(rs.getString(i++));
                schedule.setTopicId(new BigInteger(String.valueOf(rs.getInt(i++))));
                schedule.setTopicName(rs.getString(i++));
                schedule.setTopicRandomConfigId(rs.getString(i++));
                schedule.setLastUpdatedTime(rs.getTimestamp(i++));
                schedule.setProgramStatus(rs.getString(i++));
                schedule.setProgramStatusUpdatedTime(rs.getTimestamp(i++));
                schedule.setZoneId(rs.getLong(i++));
                schedule.setZoneName(rs.getString(i++));

                result.add(schedule);
            }
            return result;
        }
    }

    private class TopicBroadcastScheduleListHandler implements ResultSetHandler<List<TopicBroadcastSchedule>> {

        @Override
        public List<TopicBroadcastSchedule> handle(ResultSet rs) throws SQLException {
            List<TopicBroadcastSchedule> result = new ArrayList<TopicBroadcastSchedule>();
            while (rs.next()) {
                TopicBroadcastSchedule schedule = new TopicBroadcastSchedule();
                int i = 1;
                schedule.setId(rs.getString(i++));
                schedule.setTime(rs.getString(i++));
                schedule.setTopicId(rs.getString(i++));
                schedule.setTopicName(rs.getString(i++));
                schedule.setTopicRandomConfigId(rs.getString(i++));
                schedule.setLastUpdatedTime(rs.getTimestamp(i++));
                schedule.setZoneId(rs.getLong(i++));
                schedule.setZoneName(rs.getString(i++));
                schedule.setProgramStatus(rs.getString(i++));
                schedule.setProgramStatusUpdatedTime(rs.getTimestamp(i++));

                result.add(schedule);
            }
            return result;
        }
    }
}
